<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Window Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Advanced Features"
HREF="tutorial-advanced.html"><LINK
REL="PREVIOUS"
TITLE="Transactions"
HREF="tutorial-transactions.html"><LINK
REL="NEXT"
TITLE="Inheritance"
HREF="tutorial-inheritance.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Transactions"
HREF="tutorial-transactions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-advanced.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 3. Advanced Features</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Inheritance"
HREF="tutorial-inheritance.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="TUTORIAL-WINDOW"
>3.5. Window Functions</A
></H1
><P
>    A <I
CLASS="FIRSTTERM"
>window function</I
> performs a calculation across a set of
    table rows that are somehow related to the current row.  This is comparable
    to the type of calculation that can be done with an aggregate function.
    But unlike regular aggregate functions, use of a window function does not
    cause rows to become grouped into a single output row &mdash; the
    rows retain their separate identities.  Behind the scenes, the window
    function is able to access more than just the current row of the query
    result.
   </P
><P
>    Here is an example that shows how to compare each employee's salary
    with the average salary in his or her department:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;</PRE
><P>

</P><PRE
CLASS="SCREEN"
>  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)</PRE
><P>

    The first three output columns come directly from the table
    <TT
CLASS="STRUCTNAME"
>empsalary</TT
>, and there is one output row for each row in the
    table.  The fourth column represents an average taken across all the table
    rows that have the same <TT
CLASS="STRUCTFIELD"
>depname</TT
> value as the current row.
    (This actually is the same function as the regular <CODE
CLASS="FUNCTION"
>avg</CODE
>
    aggregate function, but the <TT
CLASS="LITERAL"
>OVER</TT
> clause causes it to be
    treated as a window function and computed across an appropriate set of
    rows.)
   </P
><P
>    A window function call always contains an <TT
CLASS="LITERAL"
>OVER</TT
> clause
    directly following the window function's name and argument(s).  This is what
    syntactically distinguishes it from a regular function or aggregate
    function.  The <TT
CLASS="LITERAL"
>OVER</TT
> clause determines exactly how the
    rows of the query are split up for processing by the window function.
    The <TT
CLASS="LITERAL"
>PARTITION BY</TT
> list within <TT
CLASS="LITERAL"
>OVER</TT
> specifies
    dividing the rows into groups, or partitions, that share the same
    values of the <TT
CLASS="LITERAL"
>PARTITION BY</TT
> expression(s).  For each row,
    the window function is computed across the rows that fall into the
    same partition as the current row.
   </P
><P
>    You can also control the order in which rows are processed by
    window functions using <TT
CLASS="LITERAL"
>ORDER BY</TT
> within <TT
CLASS="LITERAL"
>OVER</TT
>.
    (The window <TT
CLASS="LITERAL"
>ORDER BY</TT
> does not even have to match the
    order in which the rows are output.)  Here is an example:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;</PRE
><P>

</P><PRE
CLASS="SCREEN"
>  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)</PRE
><P>

    As shown here, the <CODE
CLASS="FUNCTION"
>rank</CODE
> function produces a numerical rank
    within the current row's partition for each distinct <TT
CLASS="LITERAL"
>ORDER BY</TT
>
    value, in the order defined by the <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause.
    <CODE
CLASS="FUNCTION"
>rank</CODE
> needs no explicit parameter, because its behavior
    is entirely determined by the <TT
CLASS="LITERAL"
>OVER</TT
> clause.
   </P
><P
>    The rows considered by a window function are those of the <SPAN
CLASS="QUOTE"
>"virtual
    table"</SPAN
> produced by the query's <TT
CLASS="LITERAL"
>FROM</TT
> clause as filtered by its
    <TT
CLASS="LITERAL"
>WHERE</TT
>, <TT
CLASS="LITERAL"
>GROUP BY</TT
>, and <TT
CLASS="LITERAL"
>HAVING</TT
> clauses
    if any.  For example, a row removed because it does not meet the
    <TT
CLASS="LITERAL"
>WHERE</TT
> condition is not seen by any window function.
    A query can contain multiple window functions that slice up the data
    in different ways by means of different <TT
CLASS="LITERAL"
>OVER</TT
> clauses, but
    they all act on the same collection of rows defined by this virtual table.
   </P
><P
>    We already saw that <TT
CLASS="LITERAL"
>ORDER BY</TT
> can be omitted if the ordering
    of rows is not important.  It is also possible to omit <TT
CLASS="LITERAL"
>PARTITION
    BY</TT
>, in which case there is just one partition containing all the rows.
   </P
><P
>    There is another important concept associated with window functions:
    for each row, there is a set of rows within its partition called its
    <I
CLASS="FIRSTTERM"
>window frame</I
>.  Many (but not all) window functions act only
    on the rows of the window frame, rather than of the whole partition.
    By default, if <TT
CLASS="LITERAL"
>ORDER BY</TT
> is supplied then the frame consists of
    all rows from the start of the partition up through the current row, plus
    any following rows that are equal to the current row according to the
    <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause.  When <TT
CLASS="LITERAL"
>ORDER BY</TT
> is omitted the
    default frame consists of all rows in the partition.
     <A
NAME="AEN1050"
HREF="#FTN.AEN1050"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
>
    Here is an example using <CODE
CLASS="FUNCTION"
>sum</CODE
>:
   </P
><PRE
CLASS="PROGRAMLISTING"
>SELECT salary, sum(salary) OVER () FROM empsalary;</PRE
><PRE
CLASS="SCREEN"
> salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)</PRE
><P
>    Above, since there is no <TT
CLASS="LITERAL"
>ORDER BY</TT
> in the <TT
CLASS="LITERAL"
>OVER</TT
>
    clause, the window frame is the same as the partition, which for lack of
    <TT
CLASS="LITERAL"
>PARTITION BY</TT
> is the whole table; in other words each sum is
    taken over the whole table and so we get the same result for each output
    row.  But if we add an <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause, we get very different
    results:
   </P
><PRE
CLASS="PROGRAMLISTING"
>SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;</PRE
><PRE
CLASS="SCREEN"
> salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)</PRE
><P
>    Here the sum is taken from the first (lowest) salary up through the
    current one, including any duplicates of the current one (notice the
    results for the duplicated salaries).
   </P
><P
>    Window functions are permitted only in the <TT
CLASS="LITERAL"
>SELECT</TT
> list
    and the <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause of the query. They are forbidden
    elsewhere, such as in <TT
CLASS="LITERAL"
>GROUP BY</TT
>, <TT
CLASS="LITERAL"
>HAVING</TT
>
    and <TT
CLASS="LITERAL"
>WHERE</TT
> clauses.  This is because they logically
    execute after the processing of those clauses.  Also, window functions
    execute after regular aggregate functions.  This means it is valid to
    include an aggregate function call in the arguments of a window function,
    but not vice versa.
   </P
><P
>    If there is a need to filter or group rows after the window calculations
    are performed, you can use a sub-select.  For example:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos &lt; 3;</PRE
><P>

    The above query only shows the rows from the inner query having
    <TT
CLASS="LITERAL"
>rank</TT
> less than 3.
   </P
><P
>    When a query involves multiple window functions, it is possible to write
    out each one with a separate <TT
CLASS="LITERAL"
>OVER</TT
> clause, but this is
    duplicative and error-prone if the same windowing behavior is wanted
    for several functions.  Instead, each windowing behavior can be named
    in a <TT
CLASS="LITERAL"
>WINDOW</TT
> clause and then referenced in <TT
CLASS="LITERAL"
>OVER</TT
>.
    For example:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);</PRE
><P>
   </P
><P
>    More details about window functions can be found in
    <A
HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS"
>Section 4.2.8</A
>,
    <A
HREF="functions-window.html"
>Section 9.19</A
>,
    <A
HREF="queries-table-expressions.html#QUERIES-WINDOW"
>Section 7.2.4</A
>, and the
    <A
HREF="sql-select.html"
>SELECT</A
> reference page.
   </P
></DIV
><H3
CLASS="FOOTNOTES"
>Notes</H3
><TABLE
BORDER="0"
CLASS="FOOTNOTES"
WIDTH="100%"
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN1050"
HREF="tutorial-window.html#AEN1050"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>       There are options to define the window frame in other ways, but
       this tutorial does not cover them.  See
       <A
HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS"
>Section 4.2.8</A
> for details.
      </P
></TD
></TR
></TABLE
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-transactions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="tutorial-inheritance.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Transactions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="tutorial-advanced.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Inheritance</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>